﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace LibFriesland_Campina_CRM
{
    class DalShopper
    {
        
        //Them mot Shopper moi
        public static long addShopper(BizShopper pShopper)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                Shopper shopper = new Shopper();
                shopper.Name = pShopper.name;
                shopper.Nickname = pShopper.nickname.Trim();
                shopper.Phone = pShopper.phone.Trim();
                shopper.Address = pShopper.address.Trim();
                shopper.ByNA = pShopper.by_na.Trim();
                shopper.ByShop = pShopper.by_shop;
                shopper.DateTimeCreate = pShopper.datetime_create;
                db.Shopper.InsertOnSubmit(shopper);
                db.SubmitChanges();
                return shopper.ShopperNumID;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }

        //Cap nhat thong tin Shopper
        public static int updateShopper(BizShopper pShopper)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shopper = db.Shopper.Single(p => p.ShopperNumID == pShopper.shopper_numid);
                shopper.Name = pShopper.name;
                shopper.Nickname = pShopper.nickname;
                shopper.Phone = pShopper.phone;
                shopper.Address = pShopper.address;
                db.SubmitChanges();
                return 1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        public static int updateShopper(BizShopper pShopper, BizRelatives pRelatives)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                //Cap nhat thong tin khach hang
                var shopper = db.Shopper.Single(p => p.ShopperNumID == pShopper.shopper_numid);
                shopper.Name = pShopper.name;
                shopper.Nickname = pShopper.nickname;
                shopper.Phone = pShopper.phone;
                shopper.Address = pShopper.address;
                db.SubmitChanges();

                //Cap nhat thong tin than nhan
                //Neu da ton tai thong tin than nhan
                if (pRelatives.relatives_numid != -1)
                    return pRelatives.updateRelatives();
                else
                    return pRelatives.addRelatives(); //Neu chua ton tai thong tin than nhan thi them than nhan moi
            }
            catch(Exception ex)
            {
                return -1;
            }
        }

        //Lay danh sach Shopper
        public static List<BizShopper> getAll()
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shoppers = from shopper in db.Shopper
                               select new BizShopper(shopper.ShopperNumID, shopper.Name, shopper.Nickname, shopper.Phone, shopper.Address, shopper.ByNA, shopper.ByShop);
                return shoppers.ToList();
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        //Lay shopper by shopper_numid
        public static BizShopper getShopperbyID(long pShopper_numid)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shopper = db.Shopper.Single(p=> p.ShopperNumID == pShopper_numid);
                return new BizShopper(
                                        shopper.ShopperNumID,
                                        shopper.Name,
                                        shopper.Nickname,
                                        Thuvien.myConvertNullString(shopper.Phone),
                                        Thuvien.myConvertNullString(shopper.Address),
                                        shopper.ByNA,
                                        shopper.ByShop,
                                        BizRelatives.getRelativesbyShopper(shopper.ShopperNumID)
                );
            }
            catch(Exception ex)
            {
                return null;
            }
        }

        //Lay shopper_numid theo tieu chi loc
        public static long getShopperNumID(string pBy_na, string pName, string pNickname, string pPhone, string pAddress)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                List<BizShopper> shoppers = getShopperbyNA(pBy_na);
                
                //Loc theo nickname
                shoppers = shoppers.FindAll(
                                        delegate(BizShopper item)
                                        {
                                            if (Thuvien.ConvertToUnSign(item.nickname).ToLower() == Thuvien.ConvertToUnSign(pNickname.Trim()).ToLower())
                                                return true;
                                            else
                                                return false;
                                        });
                
                //Loc theo name
                shoppers = shoppers.FindAll(
                                        delegate(BizShopper item)
                                        {
                                            if (Thuvien.ConvertToUnSign(item.name).ToLower() == Thuvien.ConvertToUnSign(pName.Trim()).ToLower())
                                                return true;
                                            else
                                                return false;
                                        });
                
                //Loc theo phone
                shoppers = shoppers.FindAll(
                                        delegate(BizShopper item)
                                        {
                                            if (item.phone == pPhone.Trim())
                                                return true;
                                            else
                                                return false;
                                        });
                //Loc theo address
                shoppers = shoppers.FindAll(
                                        delegate(BizShopper item)
                                        {
                                            if (Thuvien.ConvertToUnSign(item.address).ToLower() == Thuvien.ConvertToUnSign(pAddress.Trim()).ToLower())
                                                return true;
                                            else
                                                return false;
                                        });
                if (shoppers.Count > 0)
                    return shoppers[0].shopper_numid;
                return -1;
            }
            catch (Exception ex)
            {
                return -1;
            }
        }

        //Lay ngay tao record khach hang nho nhat
        public static DateTime getMinDateCreatebyShopper()
        {
            DalBase dalBase = new DalBase();
            string commandText = "select min(datetime_create) from shopper";
            object obj = dalBase.executeScalarCommand(commandText);
            DateTime result = Convert.ToDateTime(obj);
            return result;
        }
        
        //Tinh tong so luong san pham mua cua khach hang theo product_brand
        private static DataTable getSumProductBrandbyShopper(string pBy_na, string pBy_shop)
        {
            DataTable result = new DataTable();
            result.Columns.Add("by_shopper", typeof(long));
            result.Columns.Add("FR", typeof(Int32));
            result.Columns.Add("DL", typeof(Int32));
            DalBase dal = new DalBase();

            string commandText = " SELECT by_shopper, SUM( "
                + " CASE by_product_brand "
                + " WHEN 'FR' THEN invoice_detail.quantity ELSE 0 END ) AS FR, "
                + " SUM(CASE by_product_brand WHEN  'DL' THEN invoice_detail.quantity ELSE 0 END ) AS DL "
                + " FROM invoice_detail "
                + " INNER JOIN invoice ON invoice_detail.by_invoice = invoice.invoice_numid "
                + " INNER JOIN product ON invoice_detail.by_product = product.product_charid "
                + " INNER JOIN product_catalouge ON by_product_catalogue = product_catalogue_charid "
                + " WHERE by_na = '" + pBy_na + "'"
                + " AND by_shop ='"  + pBy_shop + "'"
                + " AND by_shopper <> 1"
                + " GROUP BY by_shopper ";

            DataTable temp = dal.getData(commandText);
            foreach (DataRow item in temp.Rows)
            {
                result.ImportRow(item);
            }

            return result;
        }

        #region Dung cho Webservice
        //Lay danh sach Shopper by NA
        public static List<BizShopper> getShopperbyNA(string pBy_na)
        {
            Friesland_Campina_CRM_DataContext db = new Friesland_Campina_CRM_DataContext(new MySqlConnection(Thuvien.getConnString()));
            try
            {
                var shoppers = from shopper in db.Shopper
                               where shopper.ByNA == pBy_na.Trim()
                               select new BizShopper(
                                                        shopper.ShopperNumID,
                                                        Thuvien.ConvertToUnSign(shopper.Name),
                                                        Thuvien.ConvertToUnSign(shopper.Nickname),
                                                        Thuvien.myConvertNullString(shopper.Phone),
                                                        Thuvien.myConvertNullString(shopper.Address),
                                                        shopper.ByNA,
                                                        shopper.ByShop,
                                                        BizRelatives.getRelativesbyShopper(shopper.ShopperNumID)
                                                     );
                return shoppers.ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        //Lay danh sach Shopper cua NA tai Shop
        public static List<BizShopper> getShopperbyNA(string pBy_na, string pBy_shop)
        {
            try
            {
                //List<BizShopper> shoppers = getShopperbyNA(pBy_na);

                ////Loc theo shop
                //shoppers = shoppers.FindAll(
                //                        delegate(BizShopper item)
                //                        {
                //                            if (item.by_shop == pBy_shop)
                //                                return true;
                //                            else
                //                                return false;
                //                        });
                //return shoppers;

                List<BizShopper> shoppers = new List<BizShopper>();
                DataTable temp = getSumProductBrandbyShopper(pBy_na, pBy_shop);
                foreach (DataRow item in temp.Rows)
                {
                    long shopperID = long.Parse(item["by_shopper"].ToString());
                    BizShopper shopper = getShopperbyID(shopperID);
                    if (int.Parse(item["FR"].ToString()) > 0)
                        shopper.fFR = "FR";
                    else
                        shopper.fFR = "";

                    if (int.Parse(item["DL"].ToString()) > 0)
                        shopper.fDL = "DL";
                    else
                        shopper.fDL = "";

                    shoppers.Add(shopper);
                }

                return shoppers;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        #endregion

        #region Dung cho Report
        //Bao cao thong tin khach hang than thiet
        public static Dataset.DatasetShopperStatistics getShopperStatistics(DateTime pFromDate, DateTime pToDate)
        {
            Dataset.DatasetShopperStatistics ds = new Dataset.DatasetShopperStatistics();
            DalBase obj = new DalBase();
            string fromDateStr = pFromDate.ToString("yyyy-MM-dd");
            string toDateStr = pToDate.ToString("yyyy-MM-dd");
            //Lay thong tin khach hang
            string commandText1 = "SELECT shopper.by_shop as shop_charid, shop.name as shop_name, shopper.shopper_numid, shopper.name "
                    + " as shopper_name, nickname as shopper_nickname, shopper.phone, relatives.relatives_numid as relatives_numid , relatives.name as relatives_name, "
                    + " relatives.year_born as relatives_year_born, na.na_charid as by_na, na.name as na_name, na.by_sup, supervisor.name as sup_name FROM shopper "
                    + " left join relatives on shopper.shopper_numid = relatives.by_shopper "
                    + " inner join na on shopper.by_na = na.na_charid "
                    + " inner join supervisor on sup_charid = na.by_sup "
                    + " inner join shop on shop.shop_charid = shopper.by_shop "
                    + " where na.by_sup <> 'SUPDEFAULT' and na.by_sup <> 'suptest' and DATE(shopper.datetime_create) >='" + fromDateStr + "' and DATE(shopper.datetime_create) <='" + toDateStr + "'"
                    + " order by by_sup, shop_charid, shopper_name, relatives_name ";
            DataTable tb1 = obj.getData(commandText1);

            //Lay thong tin so luong nhan hang mua boi khach hang
            string commandText2 = "SELECT by_shopper, "
                            + " sum(case by_product_brand when 'FR' then invoice_detail.quantity else 0 end) as BFRISO, "
                            + " sum(case by_product_brand when 'DL' then invoice_detail.quantity else 0 end)as BDLITF "
                            + " FROM invoice_detail "
                            + " inner join invoice on invoice_detail.by_invoice = invoice.invoice_numid "
                            + " inner join product on invoice_detail.by_product = product.product_charid  "
                            + " inner join product_catalouge  on by_product_catalogue = product_catalogue_charid "
                            + " where by_shopper <> 1 and by_na <> 'natest'"
                            + " group by by_shopper ";
            DataTable tb2 = obj.getData(commandText2);

            #region Tao du lieu cho bang SHOPPER
            foreach (DataRow item in tb1.Rows)
            {
                DataRow row = ds.Tables["SHOPPER"].NewRow();
                row["shop_charid"] = item["shop_charid"];
                row["shop_name"] = item["shop_name"];
                row["shopper_numid"] = item["shopper_numid"];
                row["shopper_nickname"] = item["shopper_nickname"];
                row["shopper_name"] = item["shopper_name"];
                row["phone"] = item["phone"];
                row["relatives_numid"] = item["relatives_numid"];
                row["relatives_name"] = item["relatives_name"];
                row["relatives_year_born"] = item["relatives_year_born"];
                row["by_na"] = item["by_na"];
                row["na_name"] = item["na_name"];
                row["by_sup"] = item["by_sup"];
                row["sup_name"] = item["sup_name"];
                ds.Tables["SHOPPER"].Rows.Add(row);
            }
            #endregion

            #region Tao du lieu cho bang PRODUCT_BRAND_BUY_BY_SHOPPER
            foreach (DataRow item in tb2.Rows)
            {
                DataRow row = ds.Tables["PRODUCT_BRAND_BUY_BY_SHOPPER"].NewRow();
                row["by_shopper"] = item["by_shopper"];
                row["BFRISO"] = item["BFRISO"];
                row["BDLITF"] = item["BDLITF"];
                ds.Tables["PRODUCT_BRAND_BUY_BY_SHOPPER"].Rows.Add(row);
            }
            #endregion

            return ds;
        }
        #endregion
    }
}
